package daoimpl;

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import pojo.Carrier;
import pojo.Category;
import pojo.CustomerReview;
import pojo.DeviceBrand;
import pojo.OperatingSystem;
import pojo.Overview;
import pojo.Price;
import pojo.Product;
import pojo.ProductImage;
import pojo.Specification;
import pojo.UserProfile;
import dao.CarrierDAO;
import dao.CategoryDAO;
import dao.DeviceBrandDAO;
import dao.ProductDAO;
import dao.RoleDAO;

@Repository("ProductDAO")
public class ProductDAOImpl implements ProductDAO {

	@Autowired
	private CarrierDAO CarrierDAO;
	@Autowired
	private CategoryDAO CategoryDAO;
	@Autowired
	private dao.OperatingSystemDAO OperatingSystemDAO;
	@Autowired
	private DeviceBrandDAO DeviceBrandDAO;

	private SessionFactory sessionFactory;
	
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}
	
	public List<Product> getAllProduct() {
		List<Product> ds = null;
		
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
            String hql = "Select pro from Product pro order by pro.idProduct asc";
            Query query = session.createQuery(hql);
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
	}
	
	public Product getProductBySKU(String sku) {
		Product category = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
        	String hql = "Select pro from Product pro where pro.sku=:sku";
            Query query = session.createQuery(hql);
            query.setParameter("sku", sku);
            category = (Product)query.uniqueResult();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return category;
	}

	public List<Product> getListProductFromIdCategory(String idCategory) {
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
            String hql = "Select pro from Product pro where pro.category.idcategory=:idcategory";
            Query query = session.createQuery(hql);
            query.setParameter("idcategory", idCategory);
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
	}

	public List<Product> getAllProduct(List<Price> lstPrice) {
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
        	
            String hql = "Select pro from Product pro";
            if (lstPrice != null && lstPrice.size() > 0)
            {
            	Price price = lstPrice.get(0);
            	hql += " where ( pro.price >=" + price.getStart() + " and pro.price <=" + price.getEnd() + " ) ";
            	for ( int i = 1; i < lstPrice.size(); i++)
            	{
            		hql += " or ( pro.price >=" + lstPrice.get(i).getStart() + " and pro.price <=" + lstPrice.get(i).getEnd() + " ) ";
            	}
            }
            
            Query query = session.createQuery(hql);
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
	}
	
	public List<Product> getAllProduct(List<Price> lstPrice, List<DeviceBrand> lstDeviceBrand) {
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
        	
        	boolean flag = false;
            String hql = "Select pro from Product pro";
            if (lstPrice != null && lstPrice.size() > 0)
            {
            	
            	Price price = lstPrice.get(0);
            	hql += " where ( ( pro.price >=" + price.getStart() + " and pro.price <=" + price.getEnd() + " ) ";
            	for ( int i = 1; i < lstPrice.size(); i++)
            	{
            		hql += " or ( pro.price >=" + lstPrice.get(i).getStart() + " and pro.price <=" + lstPrice.get(i).getEnd() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstDeviceBrand != null && lstDeviceBrand.size() > 0)
            {
            	DeviceBrand db = lstDeviceBrand.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	}
            	for ( int i = 1; i < lstDeviceBrand.size(); i++)
            	{
            		hql += " or ( pro.deviceBrand.id=" + lstDeviceBrand.get(i).getId() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            
            Query query = session.createQuery(hql);
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
	}
	
	public List<Product> getAllProduct(List<Price> lstPrice, List<DeviceBrand> lstDeviceBrand, List<Carrier> lstCarrier) {
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
        	
        	boolean flag = false;
            String hql = "Select pro from Product pro";
            if (lstPrice != null && lstPrice.size() > 0)
            {
            	
            	Price price = lstPrice.get(0);
            	hql += " where ( ( pro.price >=" + price.getStart() + " and pro.price <=" + price.getEnd() + " ) ";
            	for ( int i = 1; i < lstPrice.size(); i++)
            	{
            		hql += " or ( pro.price >=" + lstPrice.get(i).getStart() + " and pro.price <=" + lstPrice.get(i).getEnd() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstDeviceBrand != null && lstDeviceBrand.size() > 0)
            {
            	DeviceBrand db = lstDeviceBrand.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	}
            	for ( int i = 1; i < lstDeviceBrand.size(); i++)
            	{
            		hql += " or ( pro.deviceBrand.id=" + lstDeviceBrand.get(i).getId() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstCarrier != null && lstCarrier.size() > 0)
            {
            	Carrier ca = lstCarrier.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	}
            	for ( int i = 1; i < lstCarrier.size(); i++)
            	{
            		hql += " or ( pro.carrier.idcarrier=" + lstCarrier.get(i).getIdcarrier() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            
            Query query = session.createQuery(hql);
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
	}

	public List<Product> getAllProduct(List<Price> lstPrice, List<DeviceBrand> lstDeviceBrand, List<Carrier> lstCarrier, List<OperatingSystem> lstOperatingSystem) {
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
        	
        	boolean flag = false;
            String hql = "Select pro from Product pro";
            if (lstPrice != null && lstPrice.size() > 0)
            {
            	
            	Price price = lstPrice.get(0);
            	hql += " where ( ( pro.price >=" + price.getStart() + " and pro.price <=" + price.getEnd() + " ) ";
            	for ( int i = 1; i < lstPrice.size(); i++)
            	{
            		hql += " or ( pro.price >=" + lstPrice.get(i).getStart() + " and pro.price <=" + lstPrice.get(i).getEnd() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstDeviceBrand != null && lstDeviceBrand.size() > 0)
            {
            	DeviceBrand db = lstDeviceBrand.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	}
            	for ( int i = 1; i < lstDeviceBrand.size(); i++)
            	{
            		hql += " or ( pro.deviceBrand.id=" + lstDeviceBrand.get(i).getId() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstCarrier != null && lstCarrier.size() > 0)
            {
            	Carrier ca = lstCarrier.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	}
            	for ( int i = 1; i < lstCarrier.size(); i++)
            	{
            		hql += " or ( pro.carrier.idcarrier=" + lstCarrier.get(i).getIdcarrier() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstOperatingSystem != null && lstOperatingSystem.size() > 0)
            {
            	OperatingSystem os = lstOperatingSystem.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.operatingSystem.idos=" + os.getIdos() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.operatingSystem.idos=" + os.getIdos() + " ) ";
            	}
            	for ( int i = 1; i < lstOperatingSystem.size(); i++)
            	{
            		hql += " or ( pro.operatingSystem.idos=" + lstOperatingSystem.get(i).getIdos() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            
            Query query = session.createQuery(hql);
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
	}

	public List<Product> getAllProduct(List<Price> lstPrice, List<DeviceBrand> lstDeviceBrand, List<Carrier> lstCarrier, List<OperatingSystem> lstOperatingSystem, List<CustomerReview> lstCustomerReview, int pagecurrent, int numproductperpage) {
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
        	
        	boolean flag = false;
            String hql = "Select pro from Product pro";
            if (lstPrice != null && lstPrice.size() > 0)
            {
            	
            	Price price = lstPrice.get(0);
            	hql += " where ( ( pro.price >=" + price.getStart() + " and pro.price <=" + price.getEnd() + " ) ";
            	for ( int i = 1; i < lstPrice.size(); i++)
            	{
            		hql += " or ( pro.price >=" + lstPrice.get(i).getStart() + " and pro.price <=" + lstPrice.get(i).getEnd() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstDeviceBrand != null && lstDeviceBrand.size() > 0)
            {
            	DeviceBrand db = lstDeviceBrand.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	}
            	for ( int i = 1; i < lstDeviceBrand.size(); i++)
            	{
            		hql += " or ( pro.deviceBrand.id=" + lstDeviceBrand.get(i).getId() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstCarrier != null && lstCarrier.size() > 0)
            {
            	Carrier ca = lstCarrier.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	}
            	for ( int i = 1; i < lstCarrier.size(); i++)
            	{
            		hql += " or ( pro.carrier.idcarrier=" + lstCarrier.get(i).getIdcarrier() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstOperatingSystem != null && lstOperatingSystem.size() > 0)
            {
            	OperatingSystem os = lstOperatingSystem.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.operatingSystem.idos=" + os.getIdos() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.operatingSystem.idos=" + os.getIdos() + " ) ";
            	}
            	for ( int i = 1; i < lstOperatingSystem.size(); i++)
            	{
            		hql += " or ( pro.operatingSystem.idos=" + lstOperatingSystem.get(i).getIdos() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstCustomerReview != null && lstCustomerReview.size() > 0)
            {
            	CustomerReview cr = lstCustomerReview.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.rate>=" + cr.getValue() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.rate>=" + cr.getValue() + " ) ";
            	}
            	for ( int i = 1; i < lstCustomerReview.size(); i++)
            	{
            		hql += " or ( pro.rate>=" + lstCustomerReview.get(i).getValue() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            
            Query query = session.createQuery(hql);
            if (numproductperpage != -1)
            {
            	query.setFirstResult( pagecurrent * numproductperpage - numproductperpage);
                query.setMaxResults(Integer.valueOf(numproductperpage));
            }
            
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
	}
	
	public List<Product> getAllProduct(List<Price> lstPrice, List<DeviceBrand> lstDeviceBrand, List<Carrier> lstCarrier, List<OperatingSystem> lstOperatingSystem, List<CustomerReview> lstCustomerReview, int pagecurrent, int numproductperpage, int sortby) {
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
        	
        	boolean flag = false;
            String hql = "Select pro from Product pro";
            if (lstPrice != null && lstPrice.size() > 0)
            {
            	
            	Price price = lstPrice.get(0);
            	hql += " where ( ( pro.price >=" + price.getStart() + " and pro.price <=" + price.getEnd() + " ) ";
            	for ( int i = 1; i < lstPrice.size(); i++)
            	{
            		hql += " or ( pro.price >=" + lstPrice.get(i).getStart() + " and pro.price <=" + lstPrice.get(i).getEnd() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstDeviceBrand != null && lstDeviceBrand.size() > 0)
            {
            	DeviceBrand db = lstDeviceBrand.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	}
            	for ( int i = 1; i < lstDeviceBrand.size(); i++)
            	{
            		hql += " or ( pro.deviceBrand.id=" + lstDeviceBrand.get(i).getId() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstCarrier != null && lstCarrier.size() > 0)
            {
            	Carrier ca = lstCarrier.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	}
            	for ( int i = 1; i < lstCarrier.size(); i++)
            	{
            		hql += " or ( pro.carrier.idcarrier=" + lstCarrier.get(i).getIdcarrier() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstOperatingSystem != null && lstOperatingSystem.size() > 0)
            {
            	OperatingSystem os = lstOperatingSystem.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.operatingSystem.idos=" + os.getIdos() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.operatingSystem.idos=" + os.getIdos() + " ) ";
            	}
            	for ( int i = 1; i < lstOperatingSystem.size(); i++)
            	{
            		hql += " or ( pro.operatingSystem.idos=" + lstOperatingSystem.get(i).getIdos() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstCustomerReview != null && lstCustomerReview.size() > 0)
            {
            	CustomerReview cr = lstCustomerReview.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.rate>=" + cr.getValue() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.rate>=" + cr.getValue() + " ) ";
            	}
            	for ( int i = 1; i < lstCustomerReview.size(); i++)
            	{
            		hql += " or ( pro.rate>=" + lstCustomerReview.get(i).getValue() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            switch (sortby)
            {
            case 1:
            	hql += " order by pro.rate desc ";
            	break;
            case 2:
            	hql += " order by pro.price desc ";
            	break;
            case 3:
            	hql += " order by pro.price asc ";
            	break;
            case 4:
            	break;
            }
            
            Query query = session.createQuery(hql);
            if (numproductperpage != -1)
            {
            	query.setFirstResult( pagecurrent * numproductperpage - numproductperpage);
                query.setMaxResults(Integer.valueOf(numproductperpage));
            }
            
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
	}
	
	public List<Product> getAllProduct(List<Price> lstPrice, List<DeviceBrand> lstDeviceBrand, List<Carrier> lstCarrier, List<OperatingSystem> lstOperatingSystem, List<CustomerReview> lstCustomerReview) {
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
        	
        	boolean flag = false;
            String hql = "Select pro.sku from Product pro";
            if (lstPrice != null && lstPrice.size() > 0)
            {
            	
            	Price price = lstPrice.get(0);
            	hql += " where ( ( pro.price >=" + price.getStart() + " and pro.price <=" + price.getEnd() + " ) ";
            	for ( int i = 1; i < lstPrice.size(); i++)
            	{
            		hql += " or ( pro.price >=" + lstPrice.get(i).getStart() + " and pro.price <=" + lstPrice.get(i).getEnd() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstDeviceBrand != null && lstDeviceBrand.size() > 0)
            {
            	DeviceBrand db = lstDeviceBrand.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	}
            	for ( int i = 1; i < lstDeviceBrand.size(); i++)
            	{
            		hql += " or ( pro.deviceBrand.id=" + lstDeviceBrand.get(i).getId() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstCarrier != null && lstCarrier.size() > 0)
            {
            	Carrier ca = lstCarrier.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	}
            	for ( int i = 1; i < lstCarrier.size(); i++)
            	{
            		hql += " or ( pro.carrier.idcarrier=" + lstCarrier.get(i).getIdcarrier() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstOperatingSystem != null && lstOperatingSystem.size() > 0)
            {
            	OperatingSystem os = lstOperatingSystem.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.operatingSystem.idos=" + os.getIdos() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.operatingSystem.idos=" + os.getIdos() + " ) ";
            	}
            	for ( int i = 1; i < lstOperatingSystem.size(); i++)
            	{
            		hql += " or ( pro.operatingSystem.idos=" + lstOperatingSystem.get(i).getIdos() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            if (lstCustomerReview != null && lstCustomerReview.size() > 0)
            {
            	CustomerReview cr = lstCustomerReview.get(0);
            	if (flag == false)
            	{
            		hql += " where ( ( pro.rate>=" + cr.getValue() + " ) ";
            	}
            	else
            	{
            		hql += " and ( ( pro.rate>=" + cr.getValue() + " ) ";
            	}
            	for ( int i = 1; i < lstCustomerReview.size(); i++)
            	{
            		hql += " or ( pro.rate>=" + lstCustomerReview.get(i).getValue() + " ) ";
            	}
            	hql += " ) ";
            	flag = true;
            }
            
            
            Query query = session.createQuery(hql);
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
	}

	public List<Product> ListProdcutOrderByRate(int offset, int num) {
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
        	
            String hql = "Select pro from Product pro order by pro.rate desc";
            
            Query query = session.createQuery(hql);
            query.setFirstResult( offset);
            query.setMaxResults( num );
            
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
	}

	public List<Product> getListProductFromIdCategory(String idCategory, List<Price> lstPrice, List<DeviceBrand> lstDeviceBrand, List<Carrier> lstCarrier, List<OperatingSystem> lstOperatingSystem, List<CustomerReview> lstCustomerReview, int pagecurrent, int numproductperpage, int sortby) {
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
            String hql = "Select pro from Product pro where pro.category.idcategory=:idcategory";
            if (lstPrice != null && lstPrice.size() > 0)
            {
            	
            	Price price = lstPrice.get(0);
            	hql += " and ( ( pro.price >=" + price.getStart() + " and pro.price <=" + price.getEnd() + " ) ";
            	for ( int i = 1; i < lstPrice.size(); i++)
            	{
            		hql += " or ( pro.price >=" + lstPrice.get(i).getStart() + " and pro.price <=" + lstPrice.get(i).getEnd() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstDeviceBrand != null && lstDeviceBrand.size() > 0)
            {
            	DeviceBrand db = lstDeviceBrand.get(0);
        		hql += " and ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	for ( int i = 1; i < lstDeviceBrand.size(); i++)
            	{
            		hql += " or ( pro.deviceBrand.id=" + lstDeviceBrand.get(i).getId() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstCarrier != null && lstCarrier.size() > 0)
            {
            	Carrier ca = lstCarrier.get(0);
            	hql += " and ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	for ( int i = 1; i < lstCarrier.size(); i++)
            	{
            		hql += " or ( pro.carrier.idcarrier=" + lstCarrier.get(i).getIdcarrier() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstOperatingSystem != null && lstOperatingSystem.size() > 0)
            {
            	OperatingSystem os = lstOperatingSystem.get(0);
            	hql += " and ( ( pro.operatingSystem.idos=" + os.getIdos() + " ) ";
            	for ( int i = 1; i < lstOperatingSystem.size(); i++)
            	{
            		hql += " or ( pro.operatingSystem.idos=" + lstOperatingSystem.get(i).getIdos() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstCustomerReview != null && lstCustomerReview.size() > 0)
            {
            	CustomerReview cr = lstCustomerReview.get(0);
            	hql += " and ( ( pro.rate>=" + cr.getValue() + " ) ";
            	for ( int i = 1; i < lstCustomerReview.size(); i++)
            	{
            		hql += " or ( pro.rate>=" + lstCustomerReview.get(i).getValue() + " ) ";
            	}
            	hql += " ) ";
            }
            
            switch (sortby)
            {
            case 1:
            	hql += " order by pro.rate desc ";
            	break;
            case 2:
            	hql += " order by pro.price desc ";
            	break;
            case 3:
            	hql += " order by pro.price asc ";
            	break;
            case 4:
            	break;
            }
            
            Query query = session.createQuery(hql);
            query.setParameter("idcategory", idCategory);
            if (numproductperpage != -1)
            {
            	query.setFirstResult( pagecurrent * numproductperpage - numproductperpage);
                query.setMaxResults(Integer.valueOf(numproductperpage));
            }
            
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
	}

	public List<Product> getListProductFromIdCategory(String idCategory, List<Price> lstPrice, List<DeviceBrand> lstDeviceBrand, List<Carrier> lstCarrier, List<OperatingSystem> lstOperatingSystem, List<CustomerReview> lstCustomerReview) {
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
            String hql = "Select pro from Product pro where pro.category.idcategory=:idcategory";
            if (lstPrice != null && lstPrice.size() > 0)
            {
            	
            	Price price = lstPrice.get(0);
            	hql += " and ( ( pro.price >=" + price.getStart() + " and pro.price <=" + price.getEnd() + " ) ";
            	for ( int i = 1; i < lstPrice.size(); i++)
            	{
            		hql += " or ( pro.price >=" + lstPrice.get(i).getStart() + " and pro.price <=" + lstPrice.get(i).getEnd() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstDeviceBrand != null && lstDeviceBrand.size() > 0)
            {
            	DeviceBrand db = lstDeviceBrand.get(0);
        		hql += " and ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	for ( int i = 1; i < lstDeviceBrand.size(); i++)
            	{
            		hql += " or ( pro.deviceBrand.id=" + lstDeviceBrand.get(i).getId() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstCarrier != null && lstCarrier.size() > 0)
            {
            	Carrier ca = lstCarrier.get(0);
            	hql += " and ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	for ( int i = 1; i < lstCarrier.size(); i++)
            	{
            		hql += " or ( pro.carrier.idcarrier=" + lstCarrier.get(i).getIdcarrier() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstOperatingSystem != null && lstOperatingSystem.size() > 0)
            {
            	OperatingSystem os = lstOperatingSystem.get(0);
            	hql += " and ( ( pro.operatingSystem.idos=" + os.getIdos() + " ) ";
            	for ( int i = 1; i < lstOperatingSystem.size(); i++)
            	{
            		hql += " or ( pro.operatingSystem.idos=" + lstOperatingSystem.get(i).getIdos() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstCustomerReview != null && lstCustomerReview.size() > 0)
            {
            	CustomerReview cr = lstCustomerReview.get(0);
            	hql += " and ( ( pro.rate>=" + cr.getValue() + " ) ";
            	for ( int i = 1; i < lstCustomerReview.size(); i++)
            	{
            		hql += " or ( pro.rate>=" + lstCustomerReview.get(i).getValue() + " ) ";
            	}
            	hql += " ) ";
            }
            
            Query query = session.createQuery(hql);
            query.setParameter("idcategory", idCategory);
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
	}
	
	public boolean addProduct(Product product, int carrier, int os, String category, int deviceBrand, boolean available, List<ProductImage> productimages){
		if (getProductBySKU(product.getSku()) != null)
			return false;

		Session session = null;
		try {
			session = sessionFactory.getCurrentSession();
		} catch (HibernateException ex) {
			session = sessionFactory.openSession();
		}

		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			product.setCategory(CategoryDAO.getCategoryFromId(category));
			product.setCarrier(CarrierDAO.getCarrierByID(carrier));
			product.setDeviceBrand(DeviceBrandDAO.getDeviceBrandByID(deviceBrand));
			product.setOperatingSystem(OperatingSystemDAO.getOSByID(os));
			product.setAvailable(available);
			
			session.save(product);
			
			for (ProductImage productImage : productimages) {
				productImage.setProduct(product);
				product.getProductImages().add(productImage);
				session.saveOrUpdate(productImage);
			}
			
			if (product.getOverviews().iterator().hasNext())
			{
				Overview overview = (Overview)product.getOverviews().iterator().next();
				overview.setProduct(product);
				session.save(overview);
			}
			if (product.getSpecifications().iterator().hasNext())
			{
				Specification specification = (Specification)product.getSpecifications().iterator().next();
				specification.setProduct(product);
				session.save(specification);
			}
			
			
			transaction.commit();
		} catch (HibernateException ex) {
			transaction.rollback();
			System.err.println(ex);
			return false;
		} finally {
			session.close();
		}
		return true;
	}
	
	public boolean updateProduct(Product product, int carrier, int os, String category, int deviceBrand, boolean available){
		Session session = null;
		try {
			session = sessionFactory.getCurrentSession();
		} catch (HibernateException ex) {
			session = sessionFactory.openSession();
		}

		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			product.setCategory(CategoryDAO.getCategoryFromId(category));
			product.setCarrier(CarrierDAO.getCarrierByID(carrier));
			product.setDeviceBrand(DeviceBrandDAO.getDeviceBrandByID(deviceBrand));
			product.setOperatingSystem(OperatingSystemDAO.getOSByID(os));
			product.setAvailable(available);
			
			session.saveOrUpdate(product);
			
			if (product.getOverviews().iterator().hasNext())
			{
				Overview overview = (Overview)product.getOverviews().iterator().next();
				overview.setProduct(product);
				session.save(overview);
			}
			if (product.getSpecifications().iterator().hasNext())
			{
				Specification specification = (Specification)product.getSpecifications().iterator().next();
				specification.setProduct(product);
				session.save(specification);
			}
			
			transaction.commit();
		} catch (HibernateException ex) {
			transaction.rollback();
			System.err.println(ex);
			return false;
		} finally {
			session.close();
		}
		return true;
	}
	
	public boolean deleteProduct(Product product){
		Session session = null;
		try {
			session = sessionFactory.getCurrentSession();
		} catch (HibernateException ex) {
			session = sessionFactory.openSession();
		}

		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			
			session.delete(product);
			transaction.commit();
		} catch (HibernateException ex) {
			transaction.rollback();
			System.err.println(ex);
			return false;
		} finally {
			session.close();
		}
		return true;
	}

	public List<Product> getAllProductFromSearch(String keySearch, List<Price> lstPrice,
			List<DeviceBrand> lstDeviceBrand, List<Carrier> lstCarrier,
			List<OperatingSystem> lstOperatingSystem,
			List<CustomerReview> lstCustomerReview) {
		
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
            String hql = "Select pro from Product pro where ( pro.sku like '%" + keySearch + "%' or pro.model like '%" + keySearch + "%' or pro.name like '%" + keySearch + "%' ) ";
            if (lstPrice != null && lstPrice.size() > 0)
            {
            	
            	Price price = lstPrice.get(0);
            	hql += " and ( ( pro.price >=" + price.getStart() + " and pro.price <=" + price.getEnd() + " ) ";
            	for ( int i = 1; i < lstPrice.size(); i++)
            	{
            		hql += " or ( pro.price >=" + lstPrice.get(i).getStart() + " and pro.price <=" + lstPrice.get(i).getEnd() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstDeviceBrand != null && lstDeviceBrand.size() > 0)
            {
            	DeviceBrand db = lstDeviceBrand.get(0);
        		hql += " and ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	for ( int i = 1; i < lstDeviceBrand.size(); i++)
            	{
            		hql += " or ( pro.deviceBrand.id=" + lstDeviceBrand.get(i).getId() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstCarrier != null && lstCarrier.size() > 0)
            {
            	Carrier ca = lstCarrier.get(0);
            	hql += " and ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	for ( int i = 1; i < lstCarrier.size(); i++)
            	{
            		hql += " or ( pro.carrier.idcarrier=" + lstCarrier.get(i).getIdcarrier() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstOperatingSystem != null && lstOperatingSystem.size() > 0)
            {
            	OperatingSystem os = lstOperatingSystem.get(0);
            	hql += " and ( ( pro.operatingSystem.idos=" + os.getIdos() + " ) ";
            	for ( int i = 1; i < lstOperatingSystem.size(); i++)
            	{
            		hql += " or ( pro.operatingSystem.idos=" + lstOperatingSystem.get(i).getIdos() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstCustomerReview != null && lstCustomerReview.size() > 0)
            {
            	CustomerReview cr = lstCustomerReview.get(0);
            	hql += " and ( ( pro.rate>=" + cr.getValue() + " ) ";
            	for ( int i = 1; i < lstCustomerReview.size(); i++)
            	{
            		hql += " or ( pro.rate>=" + lstCustomerReview.get(i).getValue() + " ) ";
            	}
            	hql += " ) ";
            }
            
            Query query = session.createQuery(hql);
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
		
		
	}

	public List<Product> getAllProductFromSearch(String keySearch, List<Price> lstPrice,
			List<DeviceBrand> lstDeviceBrand, List<Carrier> lstCarrier,
			List<OperatingSystem> lstOperatingSystem,
			List<CustomerReview> lstCustomerReview, int pagecurrent,
			int numproductperpage, int sortby) {
		
		
		List<Product> ds = null;
		
		Session session = null;
		try{
			session = sessionFactory.getCurrentSession();
		}
		catch(HibernateException ex)
		{
			session = sessionFactory.openSession();
		}
			
        try {
        	String hql = "Select pro from Product pro where ( pro.sku like '%" + keySearch + "%' or pro.model like '%" + keySearch + "%' or pro.name like '%" + keySearch + "%' ) ";
            if (lstPrice != null && lstPrice.size() > 0)
            {
            	
            	Price price = lstPrice.get(0);
            	hql += " and ( ( pro.price >=" + price.getStart() + " and pro.price <=" + price.getEnd() + " ) ";
            	for ( int i = 1; i < lstPrice.size(); i++)
            	{
            		hql += " or ( pro.price >=" + lstPrice.get(i).getStart() + " and pro.price <=" + lstPrice.get(i).getEnd() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstDeviceBrand != null && lstDeviceBrand.size() > 0)
            {
            	DeviceBrand db = lstDeviceBrand.get(0);
        		hql += " and ( ( pro.deviceBrand.id=" + db.getId() + " ) ";
            	for ( int i = 1; i < lstDeviceBrand.size(); i++)
            	{
            		hql += " or ( pro.deviceBrand.id=" + lstDeviceBrand.get(i).getId() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstCarrier != null && lstCarrier.size() > 0)
            {
            	Carrier ca = lstCarrier.get(0);
            	hql += " and ( ( pro.carrier.idcarrier=" + ca.getIdcarrier() + " ) ";
            	for ( int i = 1; i < lstCarrier.size(); i++)
            	{
            		hql += " or ( pro.carrier.idcarrier=" + lstCarrier.get(i).getIdcarrier() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstOperatingSystem != null && lstOperatingSystem.size() > 0)
            {
            	OperatingSystem os = lstOperatingSystem.get(0);
            	hql += " and ( ( pro.operatingSystem.idos=" + os.getIdos() + " ) ";
            	for ( int i = 1; i < lstOperatingSystem.size(); i++)
            	{
            		hql += " or ( pro.operatingSystem.idos=" + lstOperatingSystem.get(i).getIdos() + " ) ";
            	}
            	hql += " ) ";
            }
            
            if (lstCustomerReview != null && lstCustomerReview.size() > 0)
            {
            	CustomerReview cr = lstCustomerReview.get(0);
            	hql += " and ( ( pro.rate>=" + cr.getValue() + " ) ";
            	for ( int i = 1; i < lstCustomerReview.size(); i++)
            	{
            		hql += " or ( pro.rate>=" + lstCustomerReview.get(i).getValue() + " ) ";
            	}
            	hql += " ) ";
            }
            
            switch (sortby)
            {
            case 1:
            	hql += " order by pro.rate desc ";
            	break;
            case 2:
            	hql += " order by pro.price desc ";
            	break;
            case 3:
            	hql += " order by pro.price asc ";
            	break;
            case 4:
            	break;
            }
            
            Query query = session.createQuery(hql);
            if (numproductperpage != -1)
            {
            	query.setFirstResult( pagecurrent * numproductperpage - numproductperpage);
                query.setMaxResults(Integer.valueOf(numproductperpage));
            }
            
            ds = query.list();
        } catch (HibernateException ex) {
            System.err.println(ex);
        } finally {
        	session.close();
        }
        return ds;
		
	}
}
